/*
 * 
 * Version 1.00
 * 
 * Created on 2007-6-4
 * Encoding GB2312
 */
package net.comm.user.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import net.comm.page.PageCtr;
import net.comm.shequ.db.DbWyMemberService;
import net.comm.shequ.db.WyMemberServiceSqlMysql;
import net.comm.shequ.web.form.WyMemberForm;
import net.comm.util.DatetimeUtil;
import net.comm.common.GlbFinal;
import net.comm.common.ReturnMessage;
import net.comm.common.ValueMaping;
import net.comm.common.common;

import net.comm.user.bean.BaseDetails;
import net.comm.user.bean.BaseUserSysinfo;
import net.comm.user.bean.BaseUsers;
import net.comm.user.db.DbDetailsService;
import net.comm.user.db.DbUserSysinfoService;
import net.comm.user.db.DbUsersService;
import net.comm.user.db.DetailsServiceSqlMysql;
import net.comm.user.db.UserSysinfoServiceSqlMysql;
import net.comm.user.db.UsersServiceSqlMysql;
import net.comm.user.web.form.UserSysinfoForm;
import net.comm.util.DbcpPool;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.sakaiproject.db.impl.BasicSqlService;


/**
 *
 * @author hcgong(hcgong@sina.com)
 * @version 1.0
 * @date 2007-6-4
 */
public class UserSysinfoServiceImpl {

	static Logger logger = Logger.getLogger(UserSysinfoServiceImpl.class.getName());

	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List list(UserSysinfoForm theForm,  ReturnMessage msg) 
	{
		List list = null;
		
		return list;
	}

	/**
	 * 返回根据会员ID查询的会员操作日志
	 */
	public UserSysinfoForm list(int  userID) 
	{
		Connection cn = null;
		
		UserSysinfoForm theForm = new UserSysinfoForm();

		try
		{
			cn = DbcpPool.getConnection();
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[1];
			
			fields[0] = userID;			

			UserSysinfoServiceSqlMysql servSql = new UserSysinfoServiceSqlMysql();
						
			BasicSqlService  bSqlServ = new BasicSqlService();

			/*
			 * 获得查询SQL语句
			 */
			String sql = servSql.getByUserIDSql();
									
			List list = bSqlServ.dbRead(cn, sql, fields, new DbUserSysinfoService());
								
			if(list.size() > 0){
				
				BaseUserSysinfo base = (BaseUserSysinfo)list.get(0);
				BeanUtils.copyProperties(theForm, base);
			}
			
		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}				
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return theForm;
	}

	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List adlist(UserSysinfoForm theForm,  ReturnMessage msg) 
	{
		List list = new Vector();

		Connection cn = null;
		Statement stmt=null;
		ResultSet rs=null;
		try
		{
			cn = DbcpPool.getConnection();
			
			UserSysinfoServiceSqlMysql servSql = new UserSysinfoServiceSqlMysql();
			
			StringBuffer sb = new StringBuffer(" where 1=1");

			if(theForm.getUserID() > 0 ){

				sb.append(" and UserID =");
				sb.append(theForm.getUserID() );
				sb.append(" ");
			}
			
			String orderby = theForm.getQryOrderby().replaceAll(GlbFinal.SQLINjection, " ");
			if(!orderby.equals("")){

				sb.append(" Order by ");
				sb.append(orderby );
				sb.append(" ");
			}else{
				sb.append(" Order by UserID desc ");	
			}

			//查询记录数
			stmt = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			String  sql = servSql.getCountSql1();

			logger.debug(sql+sb.toString());
			rs=stmt.executeQuery(sql+sb.toString());
			int nRows = 0;
			if (rs.next())
	           nRows = rs.getInt("COUNT");
			else
			{
	            rs.close();
	            stmt.close();
	            DbcpPool.returnConnection(cn);
			    return list;
			}
			/**
			 * 根据SQL执行结果计算分页相关信息
			 */
			PageCtr page = theForm.getPage();
			page.setTotalNum(nRows);
			page.caculate();

			sb.append(" limit ");
			sb.append(page.getPos()-1);
			sb.append(", ");
			sb.append(page.getPageSize());
			
			 //取记录
			 sql = servSql.getRecSql();
			 logger.debug(sql+sb.toString());
			 DbUserSysinfoService dbblog = new DbUserSysinfoService();
			 
			 rs=stmt.executeQuery(sql+sb.toString());
			 while (rs.next())
			 {
				try
				{
					Object obj = dbblog.readSqlResultRecord(rs);
					if (obj != null) list.add(obj);
				}
				catch (Throwable t)
				{
					logger.error(t.getMessage());
				}		
			 }
			 rs.close();
			 stmt.close();
		     DbcpPool.returnConnection(cn);

		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				DbcpPool.returnConnection(cn);
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}			
	    }finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}			
			DbcpPool.returnConnection(cn);
		}		
		return list;
	}		
	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public void get(UserSysinfoForm theForm, ReturnMessage msg) 
	{

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			DbcpPool.beginTran(cn);

			get(cn, theForm, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);			
			
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}	
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public void get(Connection cn, UserSysinfoForm theForm, ReturnMessage msg) 
	{

		try
		{
			
			//取得FORM中传入的查询参数
			Object[] fields1 = new Object[1];
			
			fields1[0] = theForm.getUserID();
						
			UserSysinfoServiceSqlMysql servSql = new UserSysinfoServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			String sql = servSql.getbykeySql();
						
			List list = bSqlServ.dbRead(cn, sql, fields1, new DbUserSysinfoService());
			if(list.size() > 0){
				BaseUserSysinfo base = (BaseUserSysinfo)list.get(0);
				BeanUtils.copyProperties(theForm, base);
				
				return;				
			}
			
			//不存在则新增一条记录
			theForm.setLevel(1);
			Object[] fields = new Object[25];
			
			fields[0] = theForm.getUserID();			//	用户ID
			fields[1] = theForm.getIntegral();		//	用户积分
			fields[2] = theForm.getLevel();			//	用户等级
			fields[3] = theForm.getAmt();			//	预存金额
			fields[4] = theForm.getComplete();		//	资料完成百分比
			fields[5] = theForm.getAttach();			//	人气指数
			fields[6] = theForm.getLinjuNum();		//	邻居数量
			fields[7] = theForm.getFirendNum();		//	好友数
			fields[8] = theForm.getBlogNum();		//	日志数量
			fields[9] = theForm.getGroupNum();		//	加入群组数
			fields[10] = theForm.getSendFoNum();		//	发贴数
			fields[11] = theForm.getRepFoNum();			//	回帖数
			fields[12] = theForm.getPartyNum();			//	参加活动次数
			fields[13] = theForm.getAlbumNum();			//	相册数量
			fields[14] = theForm.getPicNum();			//	照片数量
			fields[15] = theForm.getNemailNum();		//新私信数
			fields[16] = theForm.getInvitFrNum();		//好友请求数量
			fields[17] = theForm.getZhaohuNum();		//打招呼数量
			fields[18] = theForm.getLiuyanNum();		//新留言数量
			fields[19] = theForm.getPinlunNum();		//新评论数量
			fields[20] = theForm.getHuifuNum();			//新回复数量
			fields[21] = theForm.getJyqingNum();		//接受邀请数量
			fields[22] = theForm.getCysqNum();			//成员申请数量
			fields[23] = theForm.getTimestamp();		//排名顺序（日期/时间）
			fields[24] = DatetimeUtil.getDatetime();	//	最后修改时间		
			
			sql = servSql.getInsertSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 插入记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}	
		} finally {
		}
		
		return ;
	}

	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public void home(UserSysinfoForm theForm) 
	{

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();

			//取得积分信息
			Object[] fields1 = new Object[1];
			fields1[0] = theForm.getUserID();
						
			UserSysinfoServiceSqlMysql servSql = new UserSysinfoServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			String sql = servSql.getbykeySql();
			
			List list = bSqlServ.dbRead(cn, sql, fields1, new DbUserSysinfoService());
			if(list.size() > 0){
				BaseUserSysinfo base = (BaseUserSysinfo)list.get(0);
				BeanUtils.copyProperties(theForm, base);
			}
			//取得状态
			UsersServiceSqlMysql userservSql = new UsersServiceSqlMysql();
			
			sql = userservSql.getbykeySql();
			
			list = bSqlServ.dbRead(cn, sql, fields1, new DbUsersService());
			if(list.size() > 0){
				BaseUsers base = (BaseUsers)list.get(0);			

				theForm.setStatus(ValueMaping.GetMsg(ValueMaping.STS_FLG,base.getStatus()));
				theForm.setOnline(ValueMaping.GetMsg(ValueMaping.USER_LINESTATUS,base.getOnline()));
			}else{
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;				
			}
			//取得用户昵称和个人照片
			DetailsServiceSqlMysql detailsservSql = new DetailsServiceSqlMysql();
			sql = detailsservSql.getbykeySql();
			
			list = bSqlServ.dbRead(cn, sql, fields1, new DbDetailsService());
			if(list.size() > 0){

				BaseDetails base = (BaseDetails)list.get(0);
				
				theForm.setNick(base.getNick());
				theForm.setPicID(base.getPicID());
			}			
			
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);
			return;				
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}				
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
	
	/**
	 * 删除一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void delete(UserSysinfoForm theForm, ReturnMessage msg) 
	{
		
		return ;
	}
	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public void update(UserSysinfoForm theForm, ReturnMessage msg) 
	{

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();

			DbcpPool.beginTran(cn);
			
			update(cn, theForm, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);
			return;				
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}				
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
	
	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void update(Connection cn, UserSysinfoForm theForm, ReturnMessage msg) 
	{
		try
		{

			//取原记录的信息			
			Object[] fields2 = new Object[1];

			fields2[0] = theForm.getUserID();
			
			List list = null;
			
			UserSysinfoServiceSqlMysql servSql = new UserSysinfoServiceSqlMysql();
			
			String sql = servSql.getbykeySql();
		
			BasicSqlService  bSqlServ = new BasicSqlService();
		
			list = bSqlServ.dbRead(cn, sql, fields2, new DbUserSysinfoService());
	
			if(list.size() == 0){
				
				theForm.setLevel(1);
				//不存在则新增一条记录
				Object[] fields = new Object[25];
				
				fields[0] = theForm.getUserID();			//	用户ID
				fields[1] = theForm.getIntegral();		//	用户积分
				fields[2] = theForm.getLevel();			//	用户等级
				fields[3] = theForm.getAmt();			//	预存金额
				fields[4] = theForm.getComplete();		//	资料完成百分比
				fields[5] = theForm.getAttach();			//	人气指数
				fields[6] = theForm.getLinjuNum();		//	邻居数量
				fields[7] = theForm.getFirendNum();		//	好友数
				fields[8] = theForm.getBlogNum();		//	日志数量
				fields[9] = theForm.getGroupNum();		//	加入群组数
				fields[10] = theForm.getSendFoNum();		//	发贴数
				fields[11] = theForm.getRepFoNum();			//	回帖数
				fields[12] = theForm.getPartyNum();			//	参加活动次数
				fields[13] = theForm.getAlbumNum();			//	相册数量
				fields[14] = theForm.getPicNum();			//	照片数量
				fields[15] = theForm.getNemailNum();		//新私信数
				fields[16] = theForm.getInvitFrNum();		//好友请求数量
				fields[17] = theForm.getZhaohuNum();		//打招呼数量
				fields[18] = theForm.getLiuyanNum();		//新留言数量
				fields[19] = theForm.getPinlunNum();		//新评论数量
				fields[20] = theForm.getHuifuNum();			//新回复数量
				fields[21] = theForm.getJyqingNum();		//接受邀请数量
				fields[22] = theForm.getCysqNum();			//成员申请数量
				fields[23] = theForm.getTimestamp();		//排名顺序（日期/时间）
				fields[24] = DatetimeUtil.getDatetime();	//	最后修改时间		
				
				sql = servSql.getInsertSql();
				
				boolean  res = bSqlServ.dbWrite(cn, sql, fields);
				if(!res){
					common.setAppFailMsg(msg , " 插入记录失败，请检查！");
					DbcpPool.rollBackTran(cn);
					DbcpPool.closeTran(cn);
					DbcpPool.returnConnection(cn);
					return;
				}				
				return;				
			}
			//填充到theForm的空字段中
			BaseUserSysinfo base = (BaseUserSysinfo)list.get(0);
			
			if(theForm.getTimestamp() == 0){
				
				theForm.setTimestamp(base.getTimestamp());
			}
			int integral = theForm.getIntegral() + base.getIntegral();
			if(integral < 0){
				
				common.setAppFailMsg(msg , "本次操作需消耗积分："+theForm.getIntegral() +",目前会员积分不够");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;				
			}
			int level = 1;
			if(integral < 1000){
				level = 1;				//普通会员
			}else if(integral >= 1000 && integral < 10000){
				
				level = 2;				//认证会员
			}else if(integral >= 10000 ){
				
				level = 3;				//荣誉会员
			}
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[25];
			
			fields[0] = integral;										//	用户积分
			fields[1] = level;											//	用户等级
			fields[2] = theForm.getAmt() + base.getAmt();				//	预存金额
			fields[3] = theForm.getComplete();							//	资料完成百分比
			fields[4] = theForm.getAttach() + base.getAttach();			//	人气指数
			fields[5] = theForm.getLinjuNum() + base.getLinjuNum();		//	邻居数量
			fields[6] = theForm.getFirendNum() + base.getFirendNum();	//	好友数
			fields[7] = theForm.getBlogNum() + base.getBlogNum();		//	日志数量
			fields[8] = theForm.getGroupNum() + base.getGroupNum();		//	加入群组数
			fields[9] = theForm.getSendFoNum() + base.getSendFoNum();	//	发贴数
			fields[10] = theForm.getRepFoNum() + base.getRepFoNum();			//	回帖数
			fields[11] = theForm.getPartyNum() + base.getPartyNum();			//	参加活动次数
			fields[12] = theForm.getAlbumNum() + base.getAlbumNum();			//	相册数量
			fields[13] = theForm.getPicNum() + base.getPicNum();			//	照片数量
			fields[14] = theForm.getNemailNum() + base.getNemailNum();		//新私信数
			fields[15] = theForm.getInvitFrNum() + base.getInvitFrNum();		//好友请求数量
			fields[16] = theForm.getZhaohuNum() + base.getZhaohuNum();		//打招呼数量
			fields[17] = theForm.getLiuyanNum() + base.getLiuyanNum();		//新留言数量
			fields[18] = theForm.getPinlunNum() + base.getPinlunNum();		//新评论数量
			fields[19] = theForm.getHuifuNum() + base.getHuifuNum();			//新回复数量
			fields[20] = theForm.getJyqingNum() + base.getJyqingNum();		//接受邀请数量
			fields[21] = theForm.getCysqNum() + base.getCysqNum();			//成员申请数量
			fields[22] = theForm.getTimestamp() ;						//排名顺序（日期/时间）
			fields[23] = DatetimeUtil.getDatetime();					//	最后修改时间				
			fields[24] = theForm.getUserID();							//	用户ID
			
			
			sql = servSql.getUpdateSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 更新记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			
		} catch(Exception ex) 
		{
			logger.error("System error , param Update about userdetails" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}	
		} 
				
		return ;
	}

}
